﻿Imports System
Imports System.IO
Imports System.Text
Imports System.Configuration
Imports System.Data.SqlClient

Public Class DataCollector
    Implements IServiceDatabase, IStreamMetadata

    Private Property _ServiceSQLServer As String = "YourServer\YourDatabase"
    Private Property _ServiceDatabase As String = "VOEISPushService"
    Private Property _ServiceAuthentication As String = ""
    Private Property _ServiceUserName As String = ""
    Private Property _ServicePassword As String = ""
    Private Property _ServiceConnectionString As String
    Private Property _ServiceMaxRecords As Integer = 100
    Private Property _ServiceDBTimeout As Integer = 300
    Private Property _ServiceLastRecordDateTime As DateTime = "1/1/1900 00:00:00"
    Private Property _PushRecords As String = ""
    Private Property _SyncID As Integer = -9999
    Private Property _ProjectID As String = "0000-0000-0000-0000"
    Private Property _DataTemplateID As Integer = 0
    Private Property _FileName As String = "Default.csv"
    Private Property _LastRecordDateTime As DateTime = "1/1/1900 00:00:00"
    Private Property _TotalCols As Integer = 0
    Private Property _TotalRows As Integer = 0

    Public Property ServiceSQLServer As String Implements IServiceDatabase.ServiceSQLServer
        Get
            Return _ServiceSQLServer
        End Get
        Set(ByVal value As String)
            _ServiceSQLServer = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServiceDatabase As String Implements IServiceDatabase.ServiceDatabase
        Get
            Return _ServiceDatabase
        End Get
        Set(ByVal value As String)
            _ServiceDatabase = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServiceAuthentication As String Implements IServiceDatabase.ServiceAuthentication
        Get
            Return _ServiceAuthentication
        End Get
        Set(ByVal value As String)
            _ServiceAuthentication = value
        End Set
    End Property

    Public Property ServiceUserName As String Implements IServiceDatabase.ServiceUserName
        Get
            Return _ServiceUserName
        End Get
        Set(ByVal value As String)
            _ServiceUserName = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServicePassword As String Implements IServiceDatabase.ServicePassword
        Get
            Return "********"
        End Get
        Set(ByVal value As String)
            _ServicePassword = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServiceConnectionString As String Implements IServiceDatabase.ServiceConnectionString
        Get
            If _ServiceUserName <> "" And _ServicePassword <> "" Then
                Return "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";User Id=" & _ServiceUserName & ";Password=********;"
            Else
                Return "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";Integrated Security=SSPI;"
            End If
        End Get
        Set(ByVal value As String)
            SetConnectionString()
        End Set
    End Property

    Public Sub SetConnectionString() Implements IServiceDatabase.SetConnectionString
        If String.IsNullOrEmpty(_ServiceUserName) Or String.IsNullOrEmpty(_ServicePassword) Then
            _ServiceConnectionString = "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";Integrated Security=SSPI;"
            _ServiceAuthentication = "Windows"
        Else
            _ServiceConnectionString = "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";User Id=" & _ServiceUserName & ";Password=" & _ServicePassword & ";"
            _ServiceAuthentication = "SQL"
        End If
    End Sub

    Public Property ServiceMaxRecords As Integer Implements IServiceDatabase.ServiceMaxRecords
        Get
            Return _ServiceMaxRecords
        End Get
        Set(ByVal value As Integer)
            _ServiceMaxRecords = value
        End Set
    End Property

    Public Property ServiceDBTimeout As Integer Implements IServiceDatabase.ServiceDBTimeout
        Get
            Return _ServiceDBTimeout
        End Get
        Set(ByVal value As Integer)
            _ServiceDBTimeout = value
        End Set
    End Property

    Public Property ServiceLastRecordDateTime As DateTime Implements IServiceDatabase.ServiceLastRecordDateTime
        Get
            Return _ServiceLastRecordDateTime
        End Get
        Set(ByVal value As DateTime)
            _ServiceLastRecordDateTime = value
        End Set
    End Property

    Public Property PushRecords As String Implements IStreamMetadata.PushRecords
        Get
            Return _PushRecords
        End Get
        Set(ByVal value As String)
            _PushRecords = value
        End Set
    End Property

    Public Property SyncID As Integer Implements IStreamMetadata.SyncID
        Get
            Return _SyncID
        End Get
        Set(ByVal value As Integer)
            _SyncID = value
        End Set
    End Property

    Public Property ProjectID As String Implements IStreamMetadata.ProjectID
        Get
            Return _ProjectID
        End Get
        Set(ByVal value As String)
            _ProjectID = value
        End Set
    End Property

    Public Property DataTemplateID As Integer Implements IStreamMetadata.DataTemplateID
        Get
            Return _DataTemplateID
        End Get
        Set(ByVal value As Integer)
            _DataTemplateID = value
        End Set
    End Property

    Public Property FileName As String Implements IStreamMetadata.FileName
        Get
            Return _FileName
        End Get
        Set(ByVal value As String)
            _FileName = value
        End Set
    End Property

    Public Property LastRecordDateTime As Date Implements IStreamMetadata.LastRecordDateTime
        Get
            Return _LastRecordDateTime
        End Get
        Set(ByVal value As Date)
            _LastRecordDateTime = value
        End Set
    End Property

    Public Property TotalCols As Integer Implements IStreamMetadata.TotalCols
        Get
            Return _TotalCols
        End Get
        Set(ByVal value As Integer)
            _TotalCols = value
        End Set
    End Property

    Public Property TotalRows As Integer Implements IStreamMetadata.TotalRows
        Get
            Return _TotalRows
        End Get
        Set(ByVal value As Integer)
            _TotalRows = value
        End Set
    End Property

    Public Sub New()

        _ServiceSQLServer = My.Settings.ServiceSQLServer
        _ServiceDatabase = My.Settings.ServiceDatabase
        _ServiceAuthentication = My.Settings.ServiceAuthentication
        _ServiceUserName = My.Settings.ServiceUserName
        _ServicePassword = My.Settings.ServicePassword
        _ServiceMaxRecords = My.Settings.ServiceMaxRecords
        _ServiceDBTimeout = My.Settings.DBTimeout
        SetConnectionString()

    End Sub

    Public Function ListNewData() As ArrayList
        Dim arylstTables As New ArrayList

        Try
            'First, I need to get a connection to the database and run a stored procedure
            Using conn As New SqlConnection(_ServiceConnectionString)
                'Set and execute our command
                Using cmd As New SqlCommand("EXEC dbo.usp_GetPushRecordCount", conn)
                    'Open the connection
                    conn.Open()

                    'TODO: I had to add this to keep my archaic laptop from timing out, but even after adding, it still times out occasionally.  I probably need to look into throttling!
                    cmd.CommandTimeout = ServiceDBTimeout

                    Dim reader As SqlDataReader = Nothing 'SqlDataReader to read our stored procedure results

                    reader = cmd.ExecuteReader

                    'Read through our results and add them to the return array
                    While reader.Read()
                        If reader(1) > 0 Then
                            'Now add this file to the arraylist
                            arylstTables.Add(reader(0).ToString)
                        End If
                    End While
                End Using
            End Using
        Catch sqlex As SqlException
            'TODO: handle some of the common database exceptions here (e.g. can't connect to database, invalid stored procedure found, security exception, etc)
            System.Diagnostics.EventLog.WriteEntry("VOEIS Hub Push Service", "SQL Exception in the DataMonitor class." & Environment.NewLine & sqlex.Message.ToString, EventLogEntryType.Error)

            'TODO: Email the exception and setup the current state
            Dim mailer As New SendMail()
            mailer.SendServiceMail("VOEIS Hub Push Service - Error", "SQL Exception in the DataMonitor class." & Environment.NewLine & sqlex.Message.ToString)
            Throw sqlex
        Catch ex As Exception
            'TODO: make this a little more robust - I need to actually let the user know why this didn't work 
            System.Diagnostics.EventLog.WriteEntry("VOEIS Hub Push Service", "SQL Exception in the DataMonitor class." & Environment.NewLine & ex.Message.ToString, EventLogEntryType.Error)

            'TODO: Email the exception and setup the current state
            Dim mailer As New SendMail()
            mailer.SendServiceMail("VOEIS Hub Push Service - Error", "SQL Exception in the DataMonitor class." & Environment.NewLine & ex.Message.ToString)

            Throw ex
        End Try

        Return arylstTables
    End Function

    Public Sub SetPushRecords(ByVal TableName As String)
        Dim sb As New StringBuilder("")

        Using conn As New SqlConnection(_ServiceConnectionString)
            conn.Open()
            Using cmd As New SqlCommand("SELECT TOP " & _ServiceMaxRecords.ToString() & " * FROM dbo." & TableName & " ORDER BY CAST(LocalDateTime AS SMALLDATETIME)", conn)

                cmd.CommandTimeout = ServiceDBTimeout
                Dim reader As SqlDataReader = Nothing
                reader = cmd.ExecuteReader()

                'Set the number of columns
                TotalCols = reader.FieldCount

                'read the new records into a string that will be later used in a memory stream and sent to the hub
                While reader.Read()
                    For i As Integer = 0 To reader.FieldCount - 1
                        If i = reader.FieldCount - 1 Then
                            If i = 0 Then
                                If CType(reader(0).ToString(), DateTime) > LastRecordDateTime Then
                                    LastRecordDateTime = reader(0)
                                End If
                            End If
                            sb.Append(reader(i))
                        Else
                            If i = 0 Then
                                If CType(reader(0).ToString(), DateTime) > LastRecordDateTime Then
                                    LastRecordDateTime = reader(0)
                                End If
                            End If

                            sb.Append(reader(i) & ",")
                        End If
                    Next
                    TotalRows += 1
                    sb.Append(Environment.NewLine)
                End While

                If Not reader.IsClosed Then
                    reader.Close()
                End If
            End Using
        End Using

        _PushRecords = sb.ToString()
    End Sub

    Public Sub SetStreamMetadata(ByVal TableName As String)
        Dim StreamMetadata As New ArrayList()

        Using conn As New SqlConnection(_ServiceConnectionString)
            conn.Open()
            Using cmd As New SqlCommand("EXEC dbo.usp_GetSyncMetadataByParentTable '" & TableName & "'", conn)
                cmd.CommandTimeout = ServiceDBTimeout

                Dim reader As SqlDataReader = Nothing
                reader = cmd.ExecuteReader()

                'set the SyncID, ProjectID, DataTemplateID, and FileName from the reader
                While reader.Read()
                    _SyncID = reader(0).ToString()
                    _ProjectID = reader(1).ToString()
                    _DataTemplateID = reader(2).ToString()
                    _FileName = reader(3).ToString()
                    _ServiceLastRecordDateTime = reader(4).ToString()
                End While
            End Using
        End Using
    End Sub
End Class
